<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * @author Anh Nguyen <duyanhnguyen@vccorp.vn>
 * @copyright 2012
 */

class Item_sim_model extends CI_Model
{
//get all sim follow pagination
    public function getAll($numPost = '', $start = '')
    {
        $return = array();
        $this->db->select("I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, L.name");
         $this->db->from('item_sim I');
	    $this->db->join('list_network L', 'I.list_network_id = L.id', 'inner');
	    $this->db->where(array(
	    		'I.delete_flag'    =>  0,
	    ));
        $this->db->order_by('price', 'desc'); 
        if($numPost != '' || $start != '')
            $this->db->limit($numPost, $start);
        $rs = $this->db->get();
        $return = $rs->result_array();
        $rs->free_result();
        return $return;
    }
    public function getAllByType($numPost = '', $start = '', $type=1, $order=1)
    {
        //type = 10: sim 10 so, type = 11: sim 11 so
        $return = array();
        $this->db->select("I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, L.name");
        
        $this->db->from('item_sim I');
        $this->db->join('list_network L', 'I.list_network_id = L.id', 'inner');
        if($type == 10) {
            $this->db->like('I.phone','09', 'after');
            $this->db->or_like('I.phone','9', 'after');
            
        }
        if($type == 11) {
            $this->db->like('I.phone','01', 'after');
            $this->db->or_like('I.phone','1', 'after');
        }
        
        
        
        $this->db->where(array(
                'I.delete_flag'    =>  0,
        ));
        if($order == 2) {
            $this->db->order_by('I.price', 'ASC'); 
        } else {
            $this->db->order_by('I.price', 'DESC'); 
        }
        
        if($numPost != '' || $start != '')
            $this->db->limit($numPost, $start);
        $rs = $this->db->get();
        $return = $rs->result_array();
        $rs->free_result();

        return $return;
    }
    public function getNumAll($status = 0,$numPost = '', $start = '', $type=1, $order=1)
    {
        $return = array();
        $this->db->select("I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, L.name");
        
        $this->db->from('item_sim I');
        $this->db->join('list_network L', 'I.list_network_id = L.id', 'inner');
        if($type == 10) {
            $this->db->like('I.phone','09', 'after');
            $this->db->or_like('I.phone','9', 'after');
            
        }
        if($type == 11) {
            $this->db->like('I.phone','01', 'after');
            $this->db->or_like('I.phone','1', 'after');
        }
        

        if($status != 1) {
            $this->db->where(array(
                    'I.delete_flag'    =>  0,
            ));
        } else {
            $this->db->where(array(
                    'I.delete_flag'    =>  0,
                    'I.discount'       => $status,
            ));
        }
        if($order == 2) {
            $this->db->order_by('I.price', 'ASC'); 
        } else {
            $this->db->order_by('I.price', 'DESC'); 
        }
	    
    	return $this->db->count_all_results();
    }
//get all sim by price status : 1->giam gia, 2->tang gia, 3->Sim mới nhất
    public function getAllByPrice($priceStatus=1, $numPost = '', $start = '')
    {
    	$return = array();
    	$this->db->select("I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, L.name");
    	$this->db->from('item_sim I');
    	$this->db->join('list_network L', 'I.list_network_id = L.id', 'inner');
        
        
    	if($priceStatus !=3 ) {
	    	$this->db->where(array(
	    			'I.delete_flag'    =>  0,
	    			'I.discount'	   => $priceStatus,
	    	));
    	} 
    	$this->db->order_by('price', 'desc');
    	if($numPost != '' || $start != '')
    		$this->db->limit($numPost, $start);
    	else 
    		$this->db->limit(20, 0);
    	
    	if($priceStatus == 3) {
    		$this->db->order_by("modified", "desc");
    		
    	}
    	$rs = $this->db->get();
    	$return = $rs->result_array();
    	$rs->free_result();
    	return $return;
    	
    	
    } 
	public function getListSimByNetworkId($id, $numPost = '', $start = '',$order='desc', $type)
    {// 	id 	phone 	delete_flag 	list_city_id 	list_group_id 	list_network_id 	price
	    $return = array();
        
        $sql = 'I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, L.name '
              .'FROM item_sim I '
              .'LEFT JOIN list_network L ON I.list_network_id = L.id '
              .'WHERE I.delete_flag=0 '
              .'AND I.list_network_id =' . $id . ' ';
              
        if($type == 10) {
            $sql .= 'AND (I.phone LIKE "09%" OR I.phone LIKE "9%") ';
        } else if($type == 11) {
            $sql .= 'AND (I.phone LIKE "01%" OR I.phone LIKE "1%") ';
        }
        if($order == 2) {
            $sql = $sql.'ORDER BY I.price_update ASC ';
        } else {
            $sql = $sql.'ORDER BY I.price_update DESC ';
        }

        if($numPost != '' || $start != '')
            $sql = $sql. 'LIMIT ' . $start . ', ' . $numPost;
            
        
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $return = $query->result_array();
            $query->free_result();
            return $return;
        } else {
            $query->free_result();
            return false;
        }
        
    }
    
    public function getCountListSimByNetworkId($id,$order='desc', $type=1)
    {// 	id 	phone 	delete_flag 	list_city_id 	list_group_id 	list_network_id 	price
	    $return = array();
        $sql = 'SELECT COUNT(*) AS numrows '
              .'FROM item_sim I '
              .'LEFT JOIN list_network L ON I.list_network_id = L.id '
              .'WHERE I.delete_flag=0 '
              .'AND I.list_network_id =' . $id . ' ';
              
        if($type == 10) {
            $sql .= 'AND (I.phone LIKE "09%" OR I.phone LIKE "9%") ';
        } else if($type == 11) {
            $sql .= 'AND (I.phone LIKE "01%" OR I.phone LIKE "1%") ';
        }
        if($order == 2) {
            $sql = $sql.'ORDER BY I.price_update ASC ';
        } else {
            $sql = $sql.'ORDER BY I.price_update DESC ';
        }

        
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $return = $query->result_array();
            $query->free_result();
            return $return[0]['numrows'];
        } else {
            $query->free_result();
            return false;
        }
    
    }
    
    //get group
    public function getListSimByGroupId($id, $numPost = '', $start = '',$order='desc', $type)
    {//     id     phone     delete_flag     list_city_id     list_group_id     list_network_id     price
        $return = array();
        
        $sql = 'SELECT I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, L.name, G.name AS name_group '
              .'FROM item_sim I '
              .'LEFT JOIN list_network L ON I.list_network_id = L.id '
              .'LEFT JOIN list_group G ON I.list_network_id = G.id '
              .'WHERE I.delete_flag=0 '
              .'AND I.list_group_id =' . $id . ' ';
              
        if($type == 10) {
            $sql .= 'AND (I.phone LIKE "09%" OR I.phone LIKE "9%") ';
        } else if($type == 11) {
            $sql .= 'AND (I.phone LIKE "01%" OR I.phone LIKE "1%") ';
        }
        if($order == 2) {
            $sql = $sql.'ORDER BY I.price_update ASC ';
        } else {
            $sql = $sql.'ORDER BY I.price_update DESC ';
        }

        if($numPost != '' || $start != '')
            $sql = $sql. 'LIMIT ' . $start . ', ' . $numPost;
            
        
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $return = $query->result_array();
            $query->free_result();
            return $return;
        } else {
            $query->free_result();
            return false;
        }
        
    }
    
    public function getCountListSimByGroupId($id, $order, $type)
    {// 	id 	phone 	delete_flag 	list_city_id 	list_group_id 	list_network_id 	price
        $return = array();
        $sql = 'SELECT COUNT(*) AS numrows '
              .'FROM item_sim I '
              .'LEFT JOIN list_network L ON I.list_network_id = L.id '
              .'LEFT JOIN list_group G ON I.list_network_id = G.id '
              .'WHERE I.delete_flag=0 '
              .'AND I.list_group_id =' . $id . ' ';
              
        if($type == 10) {
            $sql .= 'AND (I.phone LIKE "09%" OR I.phone LIKE "9%") ';
        } else if($type == 11) {
            $sql .= 'AND (I.phone LIKE "01%" OR I.phone LIKE "1%") ';
        }
        if($order == 2) {
            $sql = $sql.'ORDER BY I.price_update ASC ';
        } else {
            $sql = $sql.'ORDER BY I.price_update DESC ';
        }
        
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $return = $query->result_array();
            $query->free_result();
            return $return[0]['numrows'];
        } else {
            $query->free_result();
            return false;
        }
    
    }
    public function getListSimByNetworkIdAndGroupId($id, $groupId, $numPost = '', $start = '',$order='desc', $type)
    {//     id     phone     delete_flag     list_city_id     list_group_id     list_network_id     price
        $return = array();
        
        $sql = 'SELECT I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, L.name '
              .'FROM item_sim I '
              .'LEFT JOIN list_network L ON I.list_network_id = L.id '
              .'WHERE I.delete_flag=0 '
              .'AND I.list_network_id =' . $id . ' '
              .'AND I.list_group_id = ' . $groupId . ' ';
              
        if($type == 10) {
            $sql .= 'AND (I.phone LIKE "09%" OR I.phone LIKE "9%") ';
        } else if($type == 11) {
            $sql .= 'AND (I.phone LIKE "01%" OR I.phone LIKE "1%") ';
        }
        if($order == 2) {
            $sql = $sql.'ORDER BY I.price_update ASC ';
        } else {
            $sql = $sql.'ORDER BY I.price_update DESC ';
        }

        if($numPost != '' || $start != '')
            $sql = $sql. 'LIMIT ' . $start . ', ' . $numPost;
            
        
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $return = $query->result_array();
            $query->free_result();
            return $return;
        } else {
            $query->free_result();
            return false;
        }
    }
    

    public function getCountListSimByNetworkIdAndGroupId($id, $groupId, $order=1, $type=1) {
    // 	id 	phone 	delete_flag 	list_city_id 	list_group_id 	list_network_id 	price
	    $return = array();
        
        $sql = 'SELECT COUNT(*) AS numrows '
              .'FROM item_sim I '
              .'LEFT JOIN list_network L ON I.list_network_id = L.id '
              .'WHERE I.delete_flag=0 '
              .'AND I.list_network_id =' . $id . ' '
              .'AND I.list_group_id = ' . $groupId . ' ';
              
         if($type == 10) {
            $sql .= 'AND (I.phone LIKE "09%" OR I.phone LIKE "9%") ';
        } else if($type == 11) {
            $sql .= 'AND (I.phone LIKE "01%" OR I.phone LIKE "1%") ';
        }

        $query = $this->db->query($sql);
	    if ($query->num_rows() > 0) {
            $return = $query->result_array();
            $query->free_result();
            return $return[0]['numrows'];
        } else {
            $query->free_result();
            return false;
        }
	    
    }
    public function searchArray($textSearch, $networkId, $groupId, $numPost = '',$priceStart, $priceEnd, $dauso='%', $dau=0,  $order='desc', $start = '')
    {// 	id 	phone 	delete_flag 	list_city_id 	list_group_id 	list_network_id 	price
	    $return = array();
	    $sql = 'SELECT I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, N.name '
	    	  .'FROM item_sim I '
	    	  .'LEFT JOIN list_network N ON I.list_network_id = N.id '
	    	  .'LEFT JOIN list_group G ON I.list_group_id = G.id '
	    	  .'WHERE I.delete_flag=0 '
	    	  .'AND I.phone LIKE "'.$textSearch.'" '
	    	  .'AND I.price >= "'.$priceStart.'" '
	    	  .'AND I.price <= "'.$priceEnd.'" '
	    	  .'AND (I.phone LIKE "'.$dauso.'%" OR I.phone LIKE "0'.$dauso.'%") ';
		
	    if($networkId != 0) {
	    	$sql = $sql . 'AND I.list_network_id = '.$networkId.' ';
	    }
	    if($groupId != 0) {
	    	$sql = $sql . 'AND I.list_group_id = '.$groupId . ' ';
	    } 
		if($dau == 10) {
            $sql .= 'AND (I.phone LIKE "09%" OR I.phone LIKE "9%") ';
        } else if($dau == 11) {
            $sql .= 'AND (I.phone LIKE "01%" OR I.phone LIKE "1%") ';
        }
		$sql = $sql.'ORDER BY I.price '.$order .' ';

        if($numPost != '' || $start != '')
            $sql = $sql. 'LIMIT ' . $start . ', ' . $numPost;

	    $query = $this->db->query($sql);
        
	    if ($query->num_rows() > 0) {
	    	$return = $query->result_array();
	    	$query->free_result();
	    	return $return;
	    } else {
	    	$query->free_result();
	    	return false;
	    }
    }
    public function searchCountArray($textSearch, $networkId, $groupId, $priceStart, $priceEnd, $dauso='%', $dau=0,  $order='desc')
    {//     id     phone     delete_flag     list_city_id     list_group_id     list_network_id     price
        $return = array();
        
        $sql = 'SELECT COUNT(*) AS numrows '
              .'FROM item_sim I '
              .'LEFT JOIN list_network N ON I.list_network_id = N.id '
              .'LEFT JOIN list_group G ON I.list_group_id = G.id '
              .'WHERE I.delete_flag=0 '
              .'AND I.phone LIKE "'.$textSearch.'" '
              .'AND I.price >= "'.$priceStart.'" '
              .'AND I.price <= "'.$priceEnd.'" '
              .'AND I.phone LIKE "%'.$dauso.'%" ';
        
        if($networkId != 0) {
            $sql = $sql . 'AND I.list_network_id = '.$networkId.' ';
        }
        if($groupId != 0) {
            $sql = $sql . 'AND I.list_group_id = '.$groupId . ' ';
        } 
        if($dau == 10) {
            $sql .= 'AND (I.phone LIKE "09%" OR I.phone LIKE "9%") ';
        } else if($dau == 11) {
            $sql .= 'AND (I.phone LIKE "01%" OR I.phone LIKE "1%") ';
        }
        $sql = $sql.'ORDER BY I.price '.$order .' ';

        
        $query = $this->db->query($sql);
        
        if ($query->num_rows() > 0) {
            $return = $query->result_array();
            $query->free_result();
            return $return;
        } else {
            $query->free_result();
            return false;
        }
    }
    public function searchBirthday($dateDay, $dateMonth,$dateYear, $type)
    {
        $return = array();
        $sql = 'SELECT I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, I.price_update, N.name '
              .'FROM item_sim I '
              .'LEFT JOIN list_network N ON I.list_network_id = N.id '
              .'WHERE I.delete_flag=0 ';
        if($type != 1) {
            $sql .= 'AND (I.phone LIKE "%'.$dateDay.'%" AND I.phone LIKE "%'.$dateMonth.'%" AND I.phone LIKE "%'.$dateYear.'%") ';
        }
        if($type== 1) {
            $sql .= 'AND I.phone LIKE "%'.$dateYear.'%"';
        }

        $query = $this->db->query($sql);
        
        if ($query->num_rows() > 0) {
            $return = $query->result_array();
            $query->free_result();
            return $return;
        } else {
            $query->free_result();
            return false;
        }
    }
    public function getById($id)
    {
    	$return = array();
    	$this->db->select("I.id, I.phone, I.list_city_id, I.list_group_id, I.list_network_id, I.price, L.name");
    	$this->db->from('item_sim I');
    	$this->db->join('list_network L', 'I.list_network_id = L.id', 'inner');
    	$this->db->where(array(
    			'I.delete_flag'    =>  0,
    			'I.id'=>$id,
    	));

    	$rs = $this->db->get();
    	$return = $rs->result_array();
    	$rs->free_result();
    	return $return[0];
    }
    //thong ke sim
    public function dell()
    {
	    $return = array();
	    $sql = 'DELETE FROM item_sim';
	    
	    
	    $query = $this->db->query($sql);
    
    }
    
} 


?>
